package com.zx.jhpt.service;

import com.zx.framework.core.exception.ServiceException;
import com.zx.framework.core.util.DatetimeUtils;
import com.zx.framework.core.util.StringUtil;
import com.zx.framework.db.IBaseDAO;
import com.zx.jhpt.po.Fxfkjz;
import com.zx.jhpt.po.Replymessage;
import com.zx.jhpt.util.ExcelUtil;
import com.zx.jhpt.vo.FxfkjzVo;
import com.zx.jhpt.vo.JhptConts;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Component("fxfkjzService")
@Transactional
public class FxfkjzService {

    private static final Logger LOGGER = Logger.getLogger(FxfkjzService.class);
    @Resource
    private IBaseDAO baseDAO;
    
    @Resource
	private String jhptUpload;

    
    
    public List<Fxfkjz> getAll(){
    	String hql = "from Fxfkjz f order by f.orders";
    	return baseDAO.queryHQL(hql);
    }
    
    public List<Map> analysis(FxfkjzVo example){
    	List<String> params = new ArrayList<String>();
    	
    	StringBuffer where = new StringBuffer("");
    	StringBuffer hcsjs_sql = new StringBuffer("");
    	if (null != example) {
    		if (StringUtil.isNotEmpty(example.getHcsj())) {
    			example.setHcsj(example.getHcsj()+",");
				String[] hcsjs = example.getHcsj().split(",");
				if (hcsjs.length != 0) {
					hcsjs_sql.append(" and ( ");
    				for (int i = 0; i < hcsjs.length; i++) {
    					hcsjs_sql.append(" to_char(r.hcsj, 'yyyy-MM') = ? ");
    					params.add(hcsjs[i]);
    					if (hcsjs.length -1 != i) {
    						hcsjs_sql.append(" OR ");
						}
    				}
    				hcsjs_sql.append(" ) ");
				}
			}
    		if (StringUtil.isNotEmpty(example.getFxdj())) {
    			where.append(" and f.fxdj = ? ");
    			params.add(example.getFxdj());
			}
    		if (StringUtil.isNotEmpty(example.getId())) {
    			example.setId(example.getId()+",");
    			String[] ids = example.getId().split(",");
    			if (ids.length != 0) {
    				where.append(" and ( ");
    				for (int i = 0; i < ids.length; i++) {
    					where.append(" f.id = ? ");
    					params.add(ids[i]);
    					if (ids.length -1 != i) {
    						where.append(" OR ");
						}
    				}
    				where.append(" ) ");
				}
			}
		}
    	String sql = "select f.*,r.id replid,r.deptid,r.deptname,to_char(r.hcsj,'yyyy-MM') hcsj,r.fxwt,r.gdwfzr,r.gdwzgld,r.fksj,r.sftghzd,r.gdwfkjg,r.yjzgwcsj,r.qtbz "
    			+ "from fxfkjz f "
    			+ "left join "
    			+ "(select * from replymessage r where 1=1 "+hcsjs_sql+") r "
    			+ "on f.id = r.fxid "
    			+ "where r.deptid is not null " + where
    			+ "order by f.ORDERS,r.deptid";
    	return baseDAO.querySQL(sql, params.toArray());
    }
    
    public List<Replymessage> query(FxfkjzVo example){
    	String hql = "from Replymessage where 1=1";
    	try {
    		List<Object> params = new ArrayList<>();
    		if(example!=null){
    			if(StringUtil.isNotEmpty(example.getHcsj())){
    				hql += " and hcsj = ?";
    				params.add(DatetimeUtils.stringToDate(example.getHcsj(), "yyyy-MM"));
    			}
    			if(StringUtil.isNotEmpty(example.getId())){
    				hql += " and fxid = ?";
    				params.add(example.getId());
    			}
    		}
			return baseDAO.queryHQL(hql, params.toArray());
		} catch (ParseException e) {
			e.printStackTrace();
			throw new ServiceException("时间格式转换错误！");
		}
    }
    
    public void update(Fxfkjz fxfkjz){
    	baseDAO.update(fxfkjz);
    }
    
    public HSSFWorkbook analdownload(List<Map> datas){
    	HSSFWorkbook wookBook = null;
    	// 创建Excel的工作书册 Workbook,对应到一个excel文档  
    	wookBook = new HSSFWorkbook();
    	// 创建Excel的工作sheet,对应到一个excel文档的tab  
        HSSFSheet sheet = wookBook.createSheet("风险报表");
        try {
        	HSSFFont font = wookBook.createFont();
        	font.setFontHeightInPoints((short) 10); // 字体高度
//        	font.setFontName(" 黑体 "); // 字体
            
            CellStyle cellStyle= wookBook.createCellStyle();
            cellStyle.setFont(font);
            //边框
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
            cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
            cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
            cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
            cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
            //垂直居中
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平
            //自动换行
            cellStyle.setWrapText(true);
            
//            cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
//            cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
          
            //设置行高
            sheet.createRow(0).setHeight((short)500);
            ExcelUtil.setCellValue(sheet, 0, 0, "风险点序", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 1, "一类", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 2, "二类", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 3, "部门接口", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 4, "风险点部", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 5, "风险点名", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 6, "风险监控", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 7, "风险风险等级", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 8, "筹建监控", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 9, "单位名称", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 10, "核查时间", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 11, "发现问题", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 12, "各单位负责人", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 13, "各单位主管领导", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 14, "反馈时间", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 15, "是否提供回执", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 16, "核查结果", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 17, "预计整改完成时间", 4, cellStyle);
            ExcelUtil.setCellValue(sheet, 0, 18, "整改措施", 4, cellStyle);
//            ExcelUtil.setCellValue(sheet, 0, 19, "ID", 4, cellStyle);
            String temp = "";//记录风险ID
            int row = 0,temp_row = 0; 
            for (int i = 0; i < datas.size(); i++) {
            	Map map = datas.get(i);
            	//设置行高
            	sheet.createRow(i+1).setHeight((short)500);
            	ExcelUtil.setCellValue(sheet, i+1, 0, map.get("ID"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 1, map.get("CLASS1"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 2, map.get("CLASS2"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 3, map.get("BMJKR"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 4, map.get("FXDBMFZR"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 5, map.get("FXDMC"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 6, map.get("FXJKKJ"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 7, map.get("FXDJ"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 8, map.get("CJJKPV"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 9, map.get("DEPTNAME"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 10, map.get("HCSJ"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 11, map.get("FXWT"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 12, map.get("GDWFZR"), 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 13, map.get("GDWZGLD"), 4, cellStyle);
                String fksj = "";
                if(map.get("FKSJ") != null){
                	fksj = map.get("FKSJ").toString().substring(0, 16);
                }
                ExcelUtil.setCellValue(sheet, i+1, 14, fksj, 4, cellStyle);
                String sftghzd = "";
                if(map.get("SFTGHZD") != null){
                	if (map.get("SFTGHZD").toString().equals(JhptConts.REPLYMESSAGE_SFTGHZD_TRUE)) {
                		sftghzd = "是";
					}
                	if (map.get("SFTGHZD").toString().equals(JhptConts.REPLYMESSAGE_SFTGHZD_FALSE)) {
                		sftghzd = "否";
                	}
                }
                ExcelUtil.setCellValue(sheet, i+1, 15, sftghzd, 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 16, map.get("GDWFKJG"), 4, cellStyle);
                String yjzgwcsj = "";
                if(map.get("YJZGWCSJ") != null){
                	yjzgwcsj = map.get("YJZGWCSJ").toString().substring(0, 10);
                }
                ExcelUtil.setCellValue(sheet, i+1, 17, yjzgwcsj, 4, cellStyle);
                ExcelUtil.setCellValue(sheet, i+1, 18, map.get("QTBZ"), 4, cellStyle);
//                ExcelUtil.setCellValue(sheet, i+1, 19, map.get("ID"), 4, cellStyle);
                if(StringUtil.isEmpty(temp)){
                	temp = map.get("ID").toString();
                	row = i;
                }
                boolean flag = false;
            	if (datas.size()-1 >= i+1 && !temp.equals(datas.get(i+1).get("ID").toString())) {
            		flag = true;
				}
            	if (i == datas.size() - 1) {
            		flag = true;
				}
            	if (flag) {
            		for (int j = 0; j < 9; j++) {//合并列数
            			ExcelUtil.mergeCell(sheet, row+1, i+1, j, j);
					}
            		temp = null;
				}
    		}
            //发现问题
            sheet.setColumnWidth(11, 5000);
            //反馈时间
            sheet.setColumnWidth(14, 4000);
            //各单位反馈结果
            sheet.setColumnWidth(16, 5000);
            //预计整改完成时间
            sheet.setColumnWidth(17, 5000);
            //其他备注
            sheet.setColumnWidth(18, 7000);
            
            
		} catch (Exception e) {
			LOGGER.error("生成风险报表失败：", e);
			return wookBook;
		}
        return wookBook;
    }

}
